﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using UnityScriptEngine;

using System.Drawing.Printing;

namespace EnrollmentSystem
{
    public partial class frmStatementOfAccountViewer : Form
    {

        UnityProcessor proc = new UnityProcessor();
        UnityScript[] dat = new UnityScript[0];
        Threader t = new Threader();
        UnityScript[] eudat = new UnityScript[0]; 
        
        DataTable items = new DataTable();
        DataTable itemsDetail = new DataTable();

        DataTable enrollment = new DataTable();
        DataTable salesDetail = new DataTable();
        DataTable studentprofile = new DataTable();
        DataTable payments = new DataTable();
        DataTable dStudentNumber = new DataTable();

        public frmStatementOfAccountViewer()
        {
            InitializeComponent();
            InitializeFrameSliderForm();
        }

        private void InitializeForm()
        {

            InitializeItemTable();
            btnPrint.Click +=new EventHandler(btnPrint_Click);
            proc.ProcessCompleted += new ProcessHandler(proc_ProcessCompleted);
            proc.ProcessError += new ProcessHandler(proc_ProcessError);
            btnSearch.Click += new EventHandler(btnSearch_Click);
            InitialData();
        }

        void btnSearch_Click(object sender, EventArgs e)
        {
            pnlMsg.Visible = true;
            LoadSearchData();
        }

        void proc_ProcessError(string id, string desc)
        {
            t.SafeCall(this, delegate
            {
                MessageBox.Show(desc);
                pnlMsg.Visible = false;
            });
        }

        void proc_ProcessCompleted(string id, string desc)
        {
            t.SafeCall(this, delegate
            {
                switch (id)
                {
                    case "LOADSEARCH":
                        
                        proc.DBPUX_Data_Fetch(out eudat);
                        studentprofile = eudat[0].ToDataTable();
                        enrollment = eudat[1].ToDataTable();
                        salesDetail = eudat[2].ToDataTable();
                        payments = eudat[3].ToDataTable();
                        dStudentNumber = eudat[4].ToDataTable();

                        items.Rows.Clear();
                        itemsDetail.Rows.Clear();
                        if (chkdetail.Checked == true)
                        {
                            ConstructItemsDetailed();
                            grdItems.DataSource = itemsDetail;
                            grdItems.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                            grdItems.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                            grdItems.Columns[3].DefaultCellStyle.Format = "#,#0.00";
                            grdItems.Columns[4].DefaultCellStyle.Format = "#,#0.00";
                        }
                        else
                        {
                            ConstructItems();
                            grdItems.DataSource = items;
                        }
                        
                        pnlMsg.Visible = false;
                        break;
                    case "LOAD":
                        UnityScript[] edat;
                        proc.DBPUX_Data_Fetch(out edat);
                        
                        lstAYsem.Items.Clear();
                        if (edat[0].Data_Count > 0)
                        {
                            lstAYsem.Items.AddRange(edat[0].ToList<string>(0));
                            //lstAYsem.Items.Add("All");
                            lstAYsem.SelectedIndex = 0;
                        }

                        

                        break;
                }
            });
        }

        void FormatGrid()
        {
            grdItems.Columns[0].Width = (grdItems.Width * 19) / 100;
            grdItems.Columns[1].Width = (grdItems.Width * 19) / 100;
            grdItems.Columns[2].Width = (grdItems.Width * 19) / 100;
            grdItems.Columns[3].Width = (grdItems.Width * 20) / 100;
            grdItems.Columns[4].Width = (grdItems.Width * 20) / 100;
           

            grdItems.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            grdItems.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            grdItems.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
           
            lblStatus.Text = grdItems.Rows.Count + " Records";
            grdItems.Sort(grdItems.Columns[0], ListSortDirection.Ascending);

        }

        void InitializeItemTable()
        {
            items.Columns.Add("ID Number");
            items.Columns.Add("Student Name");
            //items.Columns.Add("Enrollment ID");
            //items.Columns.Add("SY and Sem");
            items.Columns.Add("Total Amount");
            items.Columns.Add("Total Payment");
            items.Columns.Add("Total Balance");

            items.Columns[2].DataType = typeof(float);
            items.Columns[2].DefaultValue = 0.00f;
            items.Columns[3].DataType = typeof(float);
            items.Columns[3].DefaultValue = 0.00f;
            items.Columns[4].DataType = typeof(float);
            items.Columns[4].DefaultValue = 0.00f;

            itemsDetail.Columns.Add("Particulars");
            itemsDetail.Columns.Add("Date");
            itemsDetail.Columns.Add("O.R.");
            itemsDetail.Columns.Add("Amount");
            itemsDetail.Columns.Add("Balance");
            itemsDetail.Columns[3].DataType = typeof(float);
            itemsDetail.Columns[3].DefaultValue = 0.00f;
            itemsDetail.Columns[4].DataType = typeof(float);
            itemsDetail.Columns[4].DefaultValue = 0.00f;

            grdItems.DataSource = items;

            grdItems.Columns[1].DefaultCellStyle.WrapMode = DataGridViewTriState.True;

            grdItems.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

            grdItems.Columns[2].DefaultCellStyle.Format = "#,#0.00";
            grdItems.Columns[3].DefaultCellStyle.Format = "#,#0.00";
            grdItems.Columns[4].DefaultCellStyle.Format = "#,#0.00";

        }
        void InitialData()
        {
            while (proc.IsRunning)
            {
                System.Threading.Thread.Sleep(1);
            }
            proc.ProcStart();
            UnityScript x = new UnityScript("LOAD", 4);
            x.Script_Add(Script.DBPUX_BEGIN);
            x.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT DISTINCT fSchoolYear FROM tblEnrollment"));
            x.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(x);
        }
        void LoadSearchData()
        {
            
            UnityScript U = new UnityScript("LOADSEARCH", 4);
            U.Script_Add(Script.DBPUX_BEGIN);
            U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT * FROM tblstudentprofile"));
            /*
            if(lstAYsem.Text.Equals("All"))
            {
                
                U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, t1.fEnrollmentNumber, t1.fStudentNumber, ffName, fStamp FROM tblEnrollment t1, " +
                    " (SELECT fNumber, fName AS ffName FROM tblstudentprofile t22) AS t2 " +
                    "WHERE (t1.fStudentNumber = t2.fNumber)"));
            }
            else
            {*/
            U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, t1.fEnrollmentNumber, t1.fStudentNumber, ffName, fStamp FROM tblEnrollment t1, " +
                    " (SELECT fNumber, fName AS ffName FROM tblstudentprofile t22) AS t2 " +
                    "WHERE (t1.fStudentNumber = t2.fNumber) AND (fSchoolYear = '{0}')", lstAYsem.Text));
            //}
           /* U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, t1.fEnrollmentNumber, t1.fStudentNumber, ffName, SUM(ffPrice * ffQty), fStamp FROM tblEnrollment t1, "+
                " (SELECT fNumber, fName AS ffName FROM tblstudentprofile t22) AS t2 , "+
                " (SELECT fEnrollmentNum, fQty AS ffQty, fPrice AS ffPrice FROM tblSalesDetail t33) AS t3 " +
                "WHERE (t1.fStudentNumber = t2.fNumber) AND (t1.fEnrollmentNumber = t3.fEnrollmentNum) AND (fSchoolYear = '{0}')", lstAYsem.Text));*/
            U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT * FROM tblSalesDetail "));
            U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT * FROM tblpayments"));
            U.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT DISTINCT fStudentNumber FROM tblEnrollment"));
            U.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(U);
        }

        void ConstructItems()
        {
            
            for (int i = 0; i < enrollment.Rows.Count; i++)
            {
                float tP = 0, tB = 0, tT = 0;
                DataRow dr = items.NewRow();

                string eID = enrollment.Rows[i][0].ToString();
                dr[0] = enrollment.Rows[i][1].ToString();
                dr[1] = enrollment.Rows[i][2].ToString();

                for (int k = 0; k < salesDetail.Rows.Count; k++)
                {
                    if (eID.Equals(salesDetail.Rows[k]["fEnrollmentNum"].ToString()))
                    {
                        string qty = salesDetail.Rows[k]["fQty"].ToString();
                        string price = salesDetail.Rows[k]["fPrice"].ToString();

                        if (qty.Equals("")) qty = "0";
                        if (price.Equals("")) price = "0";
                        float qValue = float.Parse(qty);
                        float pValue = float.Parse(price);

                        tT += (qValue * pValue);
                    }
                }

                dr[2] = tT;
                    for (int j = 0; j < payments.Rows.Count; j++)
                    {
                        if (eID.Equals(payments.Rows[j]["fEnrollmentNumber"].ToString()))
                        {
                            float pValue = float.Parse(payments.Rows[j]["fPaymentAmount"].ToString());
                            tP += pValue;
                        }
                    }

                dr[3] = tP;
                tB = tT - tP;
                dr[4] = tB;
                items.Rows.Add(dr);
            }
        }
        struct myItems
        {
            public string name;
            public float total;
            public string[] date;
            public string[] receipt;
            public float[] amount;
            public float[] balance;
        }
        void ConstructItemsDetailed()
        {
            myItems[] item = new myItems[0];

            for (int i = 0; i < enrollment.Rows.Count; i++)
            {
                Array.Resize<myItems>(ref item, item.Length + 1);

                item[item.Length - 1].date = new string[0];
                item[item.Length - 1].receipt = new string[0];
                item[item.Length - 1].amount = new float[0];
                item[item.Length - 1].balance = new float[0];

                string eID = enrollment.Rows[i][0].ToString();
                item[item.Length - 1].name = enrollment.Rows[i][2].ToString();

                float tP = 0, tB = 0, tT = 0;
                //Get Total Amount to Pay
                for (int k = 0; k < salesDetail.Rows.Count; k++)
                {
                    if (eID.Equals(salesDetail.Rows[k]["fEnrollmentNum"].ToString()))
                    {
                        string qty = salesDetail.Rows[k]["fQty"].ToString();
                        string price = salesDetail.Rows[k]["fPrice"].ToString();

                        if (qty.Equals("")) qty = "0";
                        if (price.Equals("")) price = "0";
                        float qValue = float.Parse(qty);
                        float pValue = float.Parse(price);

                        tT += (qValue * pValue);
                    }
                }
                item[item.Length - 1].total = tT;
                tB = tT;
                for (int j = 0; j < payments.Rows.Count; j++)
                {

                    if (eID.Equals(payments.Rows[j]["fEnrollmentNumber"].ToString()))
                    {
                        Array.Resize<string>(ref item[item.Length - 1].date, item[item.Length - 1].date.Length + 1);
                        Array.Resize<string>(ref item[item.Length - 1].receipt, item[item.Length - 1].receipt.Length + 1);
                        Array.Resize<float>(ref item[item.Length - 1].amount, item[item.Length - 1].amount.Length + 1);
                        Array.Resize<float>(ref item[item.Length - 1].balance, item[item.Length - 1].balance.Length + 1);

                        float pValue = float.Parse(payments.Rows[j]["fPaymentAmount"].ToString());
                        DateTime pDate = DateTime.Parse(payments.Rows[j]["fDate"].ToString());
                        //tP += pValue;
                        item[item.Length - 1].date[item[item.Length - 1].date.Length - 1] = pDate.ToShortDateString();
                        item[item.Length - 1].receipt[item[item.Length - 1].receipt.Length - 1] = payments.Rows[j]["fOfficialReceipt"].ToString();
                        item[item.Length - 1].amount[item[item.Length - 1].amount.Length - 1] = pValue;
                        tB = tB - pValue;
                        item[item.Length - 1].balance[item[item.Length - 1].balance.Length - 1] = tB;
                    }
                }
            }

           

            for (int x = 0; x < item.Length; x++)
            {
                DataRow dr = itemsDetail.NewRow();
                dr[0] = item[x].name;
                dr[4] = item[x].total;
                itemsDetail.Rows.Add(dr);

                for (int y = 0; y < item[x].date.Length; y++)
                {
                    DataRow dr2 = itemsDetail.NewRow();
                    dr2[1] = item[x].date[y];
                    dr2[2] = item[x].receipt[y];
                    dr2[3] = item[x].amount[y];
                    dr2[4] = item[x].balance[y];
                    itemsDetail.Rows.Add(dr2);
                }
            }
        }
        //printing
        int cnt = 0;
        int pp = 1;
        void btnPrint_Click(object sender, EventArgs e)
        {
            PrintDocument pd = new PrintDocument();
            pd.DocumentName = lblText.Text;
            pd.PrintPage += new PrintPageEventHandler(pd_PrintPage);
            PrintDialog pdlg = new PrintDialog();
            pdlg.Document = pd;
            if (pdlg.ShowDialog() == DialogResult.OK)
            {
                PrintPreviewDialog ppdlg = new PrintPreviewDialog();
                ppdlg.Document = pd;
                pp = 1;
                //Tracking.EnterLog("Price Table is printed.", DateTime.Now.ToString());
                ppdlg.ShowDialog();
            }
        }

        void pd_PrintPage(object sender, PrintPageEventArgs e)
        {
            float lpp = 0;
            float y = 0;
            float tm = e.MarginBounds.Top;
            float lm = e.MarginBounds.Left - 50;
            float rm = e.PageBounds.Width - 50;
            float bm = e.PageBounds.Height - 100;
            float ht = 0;
            Font cFont;
            StringFormat sf = new StringFormat();


            //Header
            y = 110;
            cFont = new Font("Courier New", 11f, FontStyle.Bold);
            ht = cFont.GetHeight(e.Graphics);
            sf.Alignment = StringAlignment.Center;
            e.Graphics.DrawString("GOLDEN WEST COLLEGES", new Font("Courier New", 17f, FontStyle.Bold), Brushes.Black, e.PageBounds.Width / 2, 50, sf);
            e.Graphics.DrawString("Alaminos, Pangasinan", new Font("Courier New", 11f, FontStyle.Bold), Brushes.Black, e.PageBounds.Width / 2, 70, sf);
            e.Graphics.DrawString("A.Y. and SEM: "+lstAYsem.Text, new Font("Courier New", 11f, FontStyle.Underline), Brushes.Black, e.PageBounds.Width / 2, 90, sf);
            y += ht;
            sf.Alignment = StringAlignment.Near;
            e.Graphics.DrawString("Statement of Account Summary - Page " + pp, cFont, Brushes.Black, lm, y, sf);
            sf.Alignment = StringAlignment.Far;
            e.Graphics.DrawString(DateTime.Now.ToString(), cFont, Brushes.Black, rm, y, sf);

            //Column Header
            y += ht;
            y += ht;

            y += ht;
            if (chkdetail.Checked == false)
            {
                e.Graphics.DrawString("Particulars", cFont, Brushes.Black, lm + 4, y, new StringFormat());
                e.Graphics.DrawString("Total Amount", cFont, Brushes.Black, lm + 310, y, new StringFormat());
                e.Graphics.DrawString("Total Payment", cFont, Brushes.Black, lm + 460, y, new StringFormat());
                e.Graphics.DrawString("Total Balance", cFont, Brushes.Black, lm + 612, y, new StringFormat());
            }
            else
            {
                e.Graphics.DrawString("Particulars and Date", cFont, Brushes.Black, lm + 4, y, new StringFormat());
                e.Graphics.DrawString("OR", cFont, Brushes.Black, lm + 310, y, new StringFormat());
                e.Graphics.DrawString("Amount", cFont, Brushes.Black, lm + 460, y, new StringFormat());
                e.Graphics.DrawString("Balance", cFont, Brushes.Black, lm + 612, y, new StringFormat());
            }
            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm, y - (ht / 2)), new PointF(rm, y - (ht / 2)));
            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm, y + ht), new PointF(rm, y + ht));

            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm, y - (ht / 2)), new PointF(lm, y + ht));
            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(rm, y - (ht / 2)), new PointF(rm, y + ht));

            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm + 300, y - (ht / 2)), new PointF(lm + 300, y + ht));
            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm + 450, y - (ht / 2)), new PointF(lm + 450, y + ht));
            e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm + 600, y - (ht / 2)), new PointF(lm + 600, y + ht));
            
            
            //Data
            y += ht * (1.5f);
            cFont = new Font("Courier New", 10f, FontStyle.Regular);
            ht = cFont.GetHeight(e.Graphics);
            lpp = (bm - y) / ht;
            int cl = 0;

            while (cl <= lpp && cnt < grdItems.Rows.Count)
            {
                sf.Alignment = StringAlignment.Near;
                string str;
                if (chkdetail.Checked == true)
                {
                    str = grdItems.Rows[cnt].Cells[0].Value.ToString() + " " + grdItems.Rows[cnt].Cells[1].Value.ToString();
                }
                else
                {
                    str = grdItems.Rows[cnt].Cells[1].Value.ToString();
                }
                    e.Graphics.DrawString(str, cFont, Brushes.Black, lm + 1, y, sf);
                float data;
                sf.Alignment = StringAlignment.Far;
                str = grdItems.Rows[cnt].Cells[2].Value.ToString();
                if (chkdetail.Checked == true)
                    e.Graphics.DrawString(str, cFont, Brushes.Black, lm + 450, y, sf);
                else
                {
                    data = float.Parse(str);
                    e.Graphics.DrawString(data.ToString("#,#0.00"), cFont, Brushes.Black, lm + 450, y, sf);
                }
                
                str = grdItems.Rows[cnt].Cells[3].Value.ToString();
                if (str.Equals("")) str = "0";
                data = float.Parse(str);
                e.Graphics.DrawString(data.ToString("#,#0.00"), cFont, Brushes.Black, lm + 600, y, sf);

                str = grdItems.Rows[cnt].Cells[4].Value.ToString();
                if (str.Equals("")) str = "0";
                data = float.Parse(str);
                e.Graphics.DrawString(data.ToString("#,#0.00"), cFont, Brushes.Black, rm + 1, y, sf);

                e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm, y + ht), new PointF(rm, y + ht));
                e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm, y - (ht / 2)), new PointF(lm, y + ht));
                e.Graphics.DrawLine(new Pen(Color.Black), new PointF(rm, y - (ht / 2)), new PointF(rm, y + ht));

                e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm + 300, y - (ht / 2)), new PointF(lm + 300, y + ht));
                e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm + 450, y - (ht / 2)), new PointF(lm + 450, y + ht));
                e.Graphics.DrawLine(new Pen(Color.Black), new PointF(lm + 600, y - (ht / 2)), new PointF(lm + 600, y + ht));
                y += ht * (1.5f);
                cnt++;
                cl++;
            }
            //e.Graphics.DrawString("Total", cFont, Brushes.Blue, stx - 150, y + 10, sf);
            //e.Graphics.DrawString(, cFont, Brushes.Blue, stx - 4, y + 10, sf);

            if (cnt < grdItems.Rows.Count)
            {
                e.HasMorePages = true;
                pp++;
            }
            else
            {
                pp = 1;
                e.HasMorePages = false;
                cnt = 0;
            }
        }

        
    }
}
